package dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jdbc.jdbc;
import pojo.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentDaoImp implements StudentDao {
    /**
     * 显示学生表全部数据
     * @return
     */
    @Override
    public JSONArray allShow() throws SQLException {
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        String sql="SELECT * FROM schooltreasure.student;";
        PreparedStatement per=connection.prepareStatement(sql);
        ResultSet res=per.executeQuery();
        JSONArray jsonArray=new JSONArray();
        while (res.next()){
            JSONObject jsonObject=new JSONObject();
            jsonObject.put("id",res.getInt("id"));
            jsonObject.put("studentId",res.getInt("studentId"));
            jsonObject.put("studentName",res.getString("studentName"));
            jsonObject.put("classId",res.getInt("classId"));
            jsonObject.put("record",res.getInt("record"));
            jsonArray.add(jsonObject);
        }
        jdbc.guanbi(res,per,connection);
        return jsonArray;

    }

    /**
     * 新增学生表数据
     * @return
     */
    @Override
    public int insert(Student student) throws SQLException {
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        String sql="insert into schooltreasure.student(studentId,studentName,classId,record)values('"+student.getStudentId()+"','"+student.getStudentName()+"','"+student.getStudentId()+"','"+student.getRecord()+"')";
        PreparedStatement pre=connection.prepareStatement(sql);
        int res=pre.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,pre,connection);
        return 0;
    }

    /**
     * 删除修改学生表数据
     * @param student
     * @return
     * @throws SQLException
     */
    @Override
    public int del_Update(Student student) throws SQLException {
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        String sql="update schooltreasure.student set invalid='"+student.getInvalid()+"'  where id='"+student.getId()+"'";
        PreparedStatement pre=connection.prepareStatement(sql);
        int res=pre.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,pre,connection);
        return 0;
    }

    /**
     * 修改学生表数据
     * @param student
     * @return
     */
    @Override
    public int update(Student student) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="update schooltreasure.student set studentId='"+student.getStudentId()+"',studentName='"+student.getStudentName()+"',classId='"+student.getClassId()+"',record='"+student.getRecord()+"', where id='"+student.getId()+"'";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }

    /**
     * 打卡学生违纪
     * @param student
     * @return
     */
    @Override
    public int violation(Student student) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="update schooltreasure.student set record='"+student.getRecord()+"' where studentId='"+student.getStudentId()+"'";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }
}
